﻿
SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO

DECLARE @sql nvarchar(1024);
SET @sql = N'
CREATE FUNCTION [dbo].[fn_GetTimeValue]
(
    @DateTime datetime,
    @IncludeSeconds bit = 0
)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN

IF @IncludeSeconds = 0
    RETURN DATEPART(HOUR, @DateTime)*10000 + DATEPART(MINUTE, @DateTime)*100;

RETURN DATEPART(HOUR, @DateTime)*10000 + DATEPART(MINUTE, @DateTime)*100 + DATEPART(SECOND, @DateTime);

END';
IF object_id(N'dbo.fn_GetTimeValue') IS NULL
BEGIN
	exec (@sql);
END
GO


IF object_id(N'dbo.UDS_WorkAttendence_DutyTime', 'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[UDS_WorkAttendence_DutyTime] (
        [ID]            bigint          NOT NULL,
        [IDType]        nvarchar(50)    COLLATE Chinese_PRC_CI_AS NOT NULL,
        [OnDutyTime]    datetime        NOT NULL,
        [OnDutyValue]   as (dbo.fn_GetTimeValue(OnDutyTime, 0)) PERSISTED,
        [OffDutyTime]   datetime        NOT NULL,
        [OffDutyValue]  as (dbo.fn_GetTimeValue(OffDutyTime, 0)) PERSISTED,
        [IDA]           int             NOT NULL identity(1, 1),

        CONSTRAINT [PK_UDS_WorkAttendence_DutyTime] PRIMARY KEY NONCLUSTERED ([IDA] asc),
        CONSTRAINT [CK_UDS_WorkAttendence_DutyTime_ID_IDType] CHECK (([ID]=0 AND IDType=N'Company') OR ([ID]<>0 AND IDType<>N'Company')),
        CONSTRAINT [CK_UDS_WorkAttendence_DutyTime_DutyValue] CHECK (OffDutyValue > OnDutyValue)
    );
    CREATE UNIQUE CLUSTERED INDEX [IX_UDS_WorkAttendence_DutyTime_ID_IDType] ON dbo.UDS_WorkAttendence_DutyTime(IDType asc, [ID] asc);
END
GO

SET NOCOUNT ON;
IF NOT EXISTS (SELECT 1 FROM dbo.UDS_WorkAttendence_DutyTime)
BEGIN
    INSERT INTO dbo.UDS_WorkAttendence_DutyTime ([ID], IDType, OnDutyTime, OffDutyTime ) VALUES(0, N'Company', '09:00:00', '17:00:00');
END
GO